// 创建商品表
const { pool } = require('../config/db');

// 创建商品表
exports.createGoodsTable = async () => {
  const query = `
    CREATE TABLE IF NOT EXISTS goods (
      id INT AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(100) NOT NULL,
      description TEXT,
      price DECIMAL(10, 2) NOT NULL,
      image VARCHAR(255),
      tag VARCHAR(50),
      details TEXT,
      specifications JSON,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
  `;
  
  try {
    await pool.execute(query);
    console.log('Goods table created successfully!');
  } catch (error) {
    console.error('Error creating goods table:', error);
  }
};

// 获取商品列表
exports.getGoodsList = async (page = 1, pageSize = 10) => {
  // 确保参数是有效的数字类型
  const pageNum = Number.isInteger(Number(page)) ? Number(page) : 1;
  const size = Number.isInteger(Number(pageSize)) ? Number(pageSize) : 10;
  const offset = (pageNum - 1) * size;
  // 修复参数传递问题，使用字符串拼接代替参数化查询中的LIMIT和OFFSET
  const query = `SELECT * FROM goods LIMIT ${size} OFFSET ${offset}`;
  const [rows] = await pool.execute(query);
  return rows;
};

// 根据ID获取商品详情
exports.getGoodsById = async (id) => {
  const query = 'SELECT * FROM goods WHERE id = ?';
  const [rows] = await pool.execute(query, [id]);
  return rows[0] || null;
};

// 创建商品
exports.createGoods = async (goodsData) => {
  // 确保所有参数都不为undefined
  const title = goodsData.title || null;
  const description = goodsData.description || null;
  const price = goodsData.price !== undefined ? goodsData.price : null;
  const image = goodsData.image || null;
  const tag = goodsData.tag || null;
  const details = goodsData.details || null;
  const specifications = goodsData.specifications || [];
  
  const query = 'INSERT INTO goods (title, description, price, image, tag, details, specifications) VALUES (?, ?, ?, ?, ?, ?, ?)';
  const [result] = await pool.execute(query, [title, description, price, image, tag, details, JSON.stringify(specifications)]);
  return this.getGoodsById(result.insertId);
};

// 初始化示例商品数据
exports.initSampleGoods = async () => {
  const sampleGoods = [
    {
      id: 1,
      title: '琴牌展示1',
      description: '这是第一个琴牌的详细描述信息，包含了琴牌的历史背景和特点。',
      price: 899,
      image: '/pages/tabBar/home.png',
      tag: '热门',
      details: '这是商品的详细介绍内容，包括商品的材质、工艺、尺寸等详细信息。琴牌采用优质材料制作，经过多道工序精心打造而成，具有很高的收藏价值和艺术价值。',
      specifications: [
        { name: '材质', value: '优质木材' },
        { name: '尺寸', value: '20x10cm' },
        { name: '重量', value: '约300g' },
        { name: '包装', value: '精美礼盒' }
      ]
    },
    {
      id: 2,
      title: '琴牌展示2',
      description: '这是第二个琴牌的详细描述信息，展示了不同风格的琴牌设计。',
      price: 1299,
      image: '/pages/tabBar/home.png',
      tag: '新品',
      details: '这是商品的详细介绍内容，包括商品的材质、工艺、尺寸等详细信息。琴牌采用优质材料制作，经过多道工序精心打造而成，具有很高的收藏价值和艺术价值。',
      specifications: [
        { name: '材质', value: '优质木材' },
        { name: '尺寸', value: '20x10cm' },
        { name: '重量', value: '约300g' },
        { name: '包装', value: '精美礼盒' }
      ]
    },
    {
      id: 3,
      title: '琴牌展示3',
      description: '这是第三个琴牌的详细描述信息，介绍了传统工艺的琴牌制作过程。',
      price: 1599,
      image: '/pages/tabBar/home.png',
      tag: '推荐',
      details: '这是商品的详细介绍内容，包括商品的材质、工艺、尺寸等详细信息。琴牌采用优质材料制作，经过多道工序精心打造而成，具有很高的收藏价值和艺术价值。',
      specifications: [
        { name: '材质', value: '优质木材' },
        { name: '尺寸', value: '20x10cm' },
        { name: '重量', value: '约300g' },
        { name: '包装', value: '精美礼盒' }
      ]
    }
  ];
  
  // 先清空关联表中的数据（避免外键约束错误）
  await pool.execute('DELETE FROM orders');
  // 再清空商品表
  await pool.execute('DELETE FROM goods');
  
  // 插入示例数据
  for (const goods of sampleGoods) {
    await this.createGoods(goods);
  }
  
  console.log('Sample goods data initialized successfully!');
};

// 根据ID更新商品
exports.updateGoods = async (id, goodsData) => {
  // 确保所有参数都不为undefined
  const title = goodsData.title !== undefined ? goodsData.title : null;
  const description = goodsData.description !== undefined ? goodsData.description : null;
  const price = goodsData.price !== undefined ? goodsData.price : null;
  const tag = goodsData.tag !== undefined ? goodsData.tag : null;
  const details = goodsData.details !== undefined ? goodsData.details : null;
  const specifications = goodsData.specifications || [];
  
  // 如果前端提供了image字段，并且不是空字符串，则更新图片
  // 这样可以避免现有图片被清空
  if (goodsData.image !== undefined && goodsData.image !== '') {
    const query = 'UPDATE goods SET title = ?, description = ?, price = ?, image = ?, tag = ?, details = ?, specifications = ? WHERE id = ?';
    await pool.execute(query, [title, description, price, goodsData.image, tag, details, JSON.stringify(specifications), id]);
  } else {
    // 如果没有提供新的图片，则不更新image字段
    const query = 'UPDATE goods SET title = ?, description = ?, price = ?, tag = ?, details = ?, specifications = ? WHERE id = ?';
    await pool.execute(query, [title, description, price, tag, details, JSON.stringify(specifications), id]);
  }
  
  return this.getGoodsById(id);
};

// 根据ID删除商品
exports.deleteGoods = async (id) => {
  const query = 'DELETE FROM goods WHERE id = ?';
  const [result] = await pool.execute(query, [id]);
  return result.affectedRows > 0;
};